package com.lzb.convert.score;

import java.io.File;
import java.io.FileFilter;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;

import com.lzb.demo.ExcelHelper;
import com.lzb.demo.HssfExcelHelper;
import com.lzb.demo.XssfExcelHelper;
import com.lzb.domain.CustomRow_LCX;

public class Main {
	public static final String SOURCE_DIR = "demo//";
	public static final String TARGET_DIR = "done//";
	public static final String FILE_SUFFIX_XLS = "xls";
	public static final String FILE_SUFFIX_XLSX = "xlsx";
	public static void main(String[] args) throws Exception {
		String sheetNameOrSheetIndex = "0";
		boolean isCopyHeaderFormat = true;
		File sourceFileDirectory = new File(SOURCE_DIR);
		File[] sourceFiles = sourceFileDirectory.listFiles(new FileFilter() {
			@Override
			public boolean accept(File pathname) {
				if (StringUtils.endsWithIgnoreCase(pathname.getName(), FILE_SUFFIX_XLS) || StringUtils.endsWithIgnoreCase(pathname.getName(), FILE_SUFFIX_XLSX)) {
					return true;
				}
				return false;
			}
		});
		if(sourceFiles != null && sourceFiles.length != 0) {
			for (File _file : sourceFiles) {
				convert(_file, sheetNameOrSheetIndex, isCopyHeaderFormat);
			}
		}
	}

	public static void convert(File sourceFile, String sheetNameOrSheetIndex,
			boolean isCopyHeaderFormat) throws Exception {
		String fileSuffix = sourceFile.getName().substring(sourceFile.getName().lastIndexOf(".") + 1, sourceFile.getName().length());
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
		File targetFile = new File(TARGET_DIR + sourceFile.getName().substring(0, sourceFile.getName().lastIndexOf("."))
				+ "_" + format.format(new Date()) + "_done" + "." + fileSuffix);
		// 这个位置应该跟excel的表头名称位置对应
		String[] fieldNames = new String[] { "no", "name", "run", "runScore", "jump","jumpScore", "skip", "skipScore", "twoCounts", "swim", "avgScore", "totalScore" };
		//可用于验证表头栏位
		//学号	姓名	中长跑	分值	跳远		分值	跳绳	分值	两项	游泳	平时	总分			
		String[] titles = new String[]{"学号","姓名","中长跑","分值","跳远","分值","跳绳","分值","两项","游泳","平时","总分"};
		try {
			FileUtils.copyFile(sourceFile, targetFile);
		} catch (IOException e) {
			throw e;
		}
		ExcelHelper helperReader = null;
		if(FILE_SUFFIX_XLS.equalsIgnoreCase(fileSuffix)) {
			helperReader = HssfExcelHelper.getInstance(targetFile);
		} else if(FILE_SUFFIX_XLSX.equalsIgnoreCase(fileSuffix)) {
			helperReader = XssfExcelHelper.getInstance(targetFile);
		} else {
			System.out.println("未识别的文件类型[" + fileSuffix + "]");
			return;
		}
		try {
			List<CustomRow_LCX> rows = helperReader.readExcel(CustomRow_LCX.class, fieldNames, true, false, titles);
			if (rows != null && rows.size() != 0) {
				System.out.println("begin-convert:一共处理[" + rows.size() + "]条数据");
				for (int i=0; i<rows.size(); i++) {
					CustomRow_LCX _row = rows.get(i);
					_row.setNo(StringUtils.stripToEmpty(_row.getNo())); 
					_row.setName(StringUtils.stripToEmpty(_row.getName())); 
					if(StringUtils.isBlank(_row.getRunScore())) {
						_row.setRunScore("0");
					}
					if(StringUtils.isBlank(_row.getJumpScore())) {
						_row.setJumpScore("0");
					}
					if(StringUtils.isBlank(_row.getSkipScore())) {
						_row.setSkipScore("0");
					}
					if(StringUtils.isBlank(_row.getSwim())) {
						_row.setSwim("0");
					}
					if(StringUtils.isBlank(_row.getAvgScore())) {
						_row.setAvgScore("0");
					}
					BigDecimal runScore = new BigDecimal(_row.getRunScore());
					BigDecimal jumpScore = new BigDecimal(_row.getJumpScore());
					BigDecimal skipScore = new BigDecimal(_row.getSkipScore());
					BigDecimal twoCounts= new BigDecimal("0");
					BigDecimal swimScore= new BigDecimal("5");
					BigDecimal avgScore= new BigDecimal("7");
					BigDecimal totalScore= new BigDecimal("0");
					if(StringUtils.isBlank(_row.getRun()) && (StringUtils.isBlank(_row.getJump()) || StringUtils.isBlank(_row.getSkip()))) {
						_row.setRun("");
						if(StringUtils.isBlank(_row.getJump())) {
							_row.setJump("");
						}
						if(StringUtils.isBlank(_row.getSkip())) {
							_row.setSkip("");
						}
						swimScore = new BigDecimal("0");
					} 
					twoCounts = twoCounts.add(runScore);
					if(jumpScore.compareTo(skipScore) > 0) {
						twoCounts = twoCounts.add(jumpScore);
					} else {
						twoCounts = twoCounts.add(skipScore);
					}
					//除以2再乘以0.35
					twoCounts = twoCounts.divide(new BigDecimal("2")).multiply(new BigDecimal("0.35")).setScale(2, BigDecimal.ROUND_HALF_UP);
					_row.setTwoCounts(twoCounts.toString());
					//两项成绩 + 平时成绩 + 游泳成绩
					totalScore = totalScore.add(avgScore).add(swimScore).add(twoCounts).setScale(2, BigDecimal.ROUND_HALF_UP);
					_row.setTotalScore(totalScore.toString());
					_row.setNo(String.valueOf(i + 1));
					_row.setSwim(swimScore.toString());
					_row.setAvgScore(avgScore.toString());
				}
				System.out.println("end-convert:一共处理[" + rows.size() + "]条数据");
				ExcelHelper helperWriter = null;
				if(FILE_SUFFIX_XLS.equalsIgnoreCase(fileSuffix)) {
					helperWriter = HssfExcelHelper.getInstance(targetFile);
				} else if(FILE_SUFFIX_XLSX.equalsIgnoreCase(fileSuffix)) {
					helperWriter = XssfExcelHelper.getInstance(targetFile);
				} else {
					System.out.println("未识别的文件类型[" + fileSuffix + "]");
					return;
				}
				helperWriter.writeExcel(CustomRow_LCX.class, rows, fieldNames, null, sheetNameOrSheetIndex,
						isCopyHeaderFormat);
				System.out.println(sourceFile.getAbsolutePath() + " 转换完成，文件路径：[" + targetFile.getAbsolutePath() + "]");
				//删除demo目录文件
				FileUtils.forceDeleteOnExit(sourceFile);
				calculateAvg(rows);
			} else {
				//删除done目录的文件
				FileUtils.forceDeleteOnExit(targetFile);
			}
		} catch (Exception e) {
			throw e;
		}
	}
	
	public static void calculateAvg(List<CustomRow_LCX> rows) {
		Map<String, String> avgs = new HashMap<String, String>();
		//中长跑，跳绳，跳远，两项，总分
		BigDecimal runScoreAvg = new BigDecimal("0");
		BigDecimal skipScoreAvg = new BigDecimal("0");
		BigDecimal jumpScoreAvg = new BigDecimal("0");
		BigDecimal twoCountScoreAvg = new BigDecimal("0");
		BigDecimal totalScoreAvg = new BigDecimal("0");
		if(rows != null) {
			int runCount = 0;
			int skipCount = 0;
			int jumpCount = 0;
			for(CustomRow_LCX _row : rows) {
				if(StringUtils.isNoneBlank(_row.getRun())) {
					runCount++;
					runScoreAvg = new BigDecimal(_row.getRunScore()).add(runScoreAvg);
				}
				if(StringUtils.isNoneBlank(_row.getSkip())) {
					skipCount++;
					skipScoreAvg = new BigDecimal(_row.getSkipScore()).add(skipScoreAvg);
				}
				if(StringUtils.isNoneBlank(_row.getJump())) {
					jumpCount++;
					jumpScoreAvg = new BigDecimal(_row.getJumpScore()).add(jumpScoreAvg);
				}
				twoCountScoreAvg = twoCountScoreAvg.add(new BigDecimal(_row.getTwoCounts()));
				totalScoreAvg = totalScoreAvg.add(new BigDecimal(_row.getTotalScore()));
			}
			avgs.put("中长跑", runScoreAvg.divide(new BigDecimal(String.valueOf(runCount)), 2).toString());
			avgs.put("跳绳", skipScoreAvg.divide(new BigDecimal(String.valueOf(skipCount)), 2).toString());
			avgs.put("跳远", jumpScoreAvg.divide(new BigDecimal(String.valueOf(jumpCount)), 2).toString());
			avgs.put("两项", twoCountScoreAvg.divide(new BigDecimal(String.valueOf(rows.size())), 2).toString());
			avgs.put("总分", totalScoreAvg.divide(new BigDecimal(String.valueOf(rows.size())), 2).toString());
		}
		System.out.println(avgs);
		//return avgs;
	}
}
